package com.tables.get

import com.intellij.openapi.actionSystem.AnAction
import com.intellij.openapi.actionSystem.AnActionEvent
import com.intellij.openapi.fileEditor.OpenFileDescriptor
import com.intellij.openapi.project.Project
import com.intellij.openapi.vfs.VirtualFile
import com.intellij.psi.search.FilenameIndex
import com.intellij.psi.search.GlobalSearchScope
import com.intellij.ui.components.JBTabbedPane
import com.intellij.ui.table.JBTable
import java.awt.BorderLayout
import java.awt.Toolkit
import java.awt.datatransfer.StringSelection
import java.awt.event.MouseAdapter
import java.awt.event.MouseEvent
import javax.swing.*
import java.io.InputStreamReader
import java.io.BufferedReader
import javax.swing.JDialog
import javax.swing.table.DefaultTableModel

class TableGetAction : AnAction()  {

    private val sqlKeywords = listOf(
        // ========== DDL (Data Definition) ==========
        // Table operation
        "CREATE\\s+(?:TEMPORARY\\s+)?TABLE",
        "ALTER\\s+TABLE",
        "DROP\\s+TABLE",
        "TRUNCATE\\s+TABLE",
        "RENAME\\s+TABLE",  // MySQL
        "ALTER\\s+TABLE.*?\\s+RENAME\\s+TO",  // PostgreSQL/Oracle
        "SP_RENAME\\s+'.*?'",  // SQL Server Rename stored procedure

        // Index operation (cross database)
        "CREATE\\s+(?:UNIQUE\\s+)?(?:CLUSTERED\\s+)?INDEX.*?ON",
        "DROP\\s+INDEX.*?ON",  // MySQL/SQL Server
        "DROP\\s+INDEX.*?\\s+FROM",  // PostgreSQL

        // View/Sequence
        "CREATE\\s+(?:OR\\s+REPLACE\\s+)?VIEW",
        "CREATE\\s+SEQUENCE",  // Oracle/PostgreSQL
        "ALTER\\s+SEQUENCE",

        // ========== DML (Data operation) ==========
        // Basic CRUD
        "SELECT\\s+[\\s\\S]*?\\s+FROM",
        "INSERT\\s+(?:INTO)?",
        "UPDATE",
        "DELETE\\s+(?:FROM)?",
        "MERGE\\s+(?:INTO)?",  // Oracle/SQL Server

        // join query
        "(?:INNER|LEFT|RIGHT|FULL|OUTER)\\s+JOIN",
        "CROSS\\s+JOIN",
        "NATURAL\\s+JOIN",
        "CROSS\\s+APPLY",  // SQL Server

        // Analysis/Execution Plan
        "EXPLAIN\\s+(?:SELECT|UPDATE|DELETE|INSERT).*?FROM",
        "ANALYZE\\s+TABLE",  // MySQL/PostgreSQL
        "DBCC\\s+SHOW_STATISTICS",  // SQL Server

        // Common table expression
        "WITH\\s+\\w+\\s+AS",
        "WITH\\s+RECURSIVE",  // PostgreSQL递归查询

        // ========== DCL (Data control) ==========
        // Rights Management
        "GRANT\\s+.+?ON\\s+(?:TABLE\\s+)?",
        "REVOKE\\s+.+?ON\\s+(?:TABLE\\s+)?",
        "DENY\\s+.+?ON",  // SQL Server

        // Lock control
        "LOCK\\s+TABLE",  // PostgreSQL/MySQL
        "SELECT.*?\\s+FOR\\s+(?:UPDATE|SHARE)",  // ROW LEVEL LOCK
        "BEGIN\\s+TRAN.*?WITH\\s+\\w+",  // SQL Server isolation level

        // ========== Other cross database operations ==========
        "COMMENT\\s+ON\\s+TABLE",  // PostgreSQL
        "EXEC\\s+sp_addextendedproperty",  // SQL Server table Annotations
        "PURGE\\s+TABLE",  // Oracle recycle bin
        "FLASHBACK\\s+TABLE"  // Oracle Flashback
    )

    private val mybatisAnnotations = setOf(
        "org.apache.ibatis.annotations.Select",
        "org.apache.ibatis.annotations.Update",
        "org.apache.ibatis.annotations.Delete",
        "org.apache.ibatis.annotations.Insert",
        "com.baomidou.mybatisplus.annotation.TableName",
        "javax.persistence.Table",
        "Select",
        "Update",
        "Delete",
        "Insert",
        "TableName",
        "Table"
    )
    private val virtualFiles = mutableSetOf<VirtualFile>()
    override fun actionPerformed(p0: AnActionEvent) {
        val project = p0.project ?: return
        val tables = mutableSetOf<String>()

        processFiles(project, "xml") { file ->
            val content = readFileContent(file)
            extractFromSql(content, tables, file)
        }

        processFiles(project, "java") { file ->
            val content = readFileContent(file)
            extractFromSql(content, tables, file)
            extractFromAnnotations(content, tables, file)
        }

        showResults(tables.sorted(), project)
    }

    private fun processFiles(project: Project, ext: String, processor: (VirtualFile) -> Unit) {
        val files = FilenameIndex.getAllFilesByExt(project, ext, GlobalSearchScope.projectScope(project))
        files.forEach(processor)
    }

    private fun readFileContent(file: VirtualFile): String {
        val reader = BufferedReader(InputStreamReader(file.inputStream))
        val stringBuilder = StringBuilder()
        var line: String?
        while (reader.readLine().also { line = it } != null) {
            stringBuilder.append(line).append("\n")
        }
        reader.close()
        return stringBuilder.toString()
    }

    private fun extractFromSql(content: String, tables: MutableSet<String>,file: VirtualFile) {
        val pattern = """
            (?xi)
            (?<!<)\b
            (${sqlKeywords.joinToString("|")})
            \b\s+
            (
                [a-z_][\w.]*      # Regular table name
                |`[^`]+`          # MySQL quotation mark table name
                |\[([^\]]+)\]     # SQL Server square bracket table name
                |\"[^\"]+\"       # PostgreSQL Double Quote Table Names
            )
        """.trimIndent().toRegex()
        pattern.findAll(content).forEach {
            val tableName = it.groupValues[2].replace("[\"`']".toRegex(), "")
            if (!tableName.matches("\\d+".toRegex())) {
                tables.add(tableName)
                virtualFiles.add(file)
            }
        }
    }

    private fun extractFromAnnotations(content: String, tables: MutableSet<String>, file: VirtualFile) {
        val annotationPattern = Regex("@(${mybatisAnnotations.joinToString("|")})\\s*\\((.*?)\\)")
        annotationPattern.findAll(content).forEach { matchResult ->
            val annotationContent = matchResult.groupValues[2]
            when (matchResult.groupValues[1]) {
                "javax.persistence.Table" -> {
                    val namePattern = Regex("name\\s*=\\s*[\"']([^\"']+)[\"']")
                    namePattern.find(annotationContent)?.groupValues?.getOrNull(1)?.let {
                        tables.add(it)
                        virtualFiles.add(file)
                    }
                }
                "Table" -> {
                    val namePattern = Regex("name\\s*=\\s*[\"']([^\"']+)[\"']")
                    namePattern.find(annotationContent)?.groupValues?.getOrNull(1)?.let {
                        tables.add(it)
                        virtualFiles.add(file)
                    }
                }
                "com.baomidou.mybatisplus.annotation.TableName" -> {
                    val valuePattern = Regex("(?:value\\s*=\\s*)?[\"']([^\"']+)[\"']")
                    valuePattern.find(annotationContent)?.groupValues?.getOrNull(1)?.let {
                        tables.add(it)
                        virtualFiles.add(file)
                    }
                }
                "TableName" -> {
                    val valuePattern = Regex("(?:value\\s*=\\s*)?[\"']([^\"']+)[\"']")
                    valuePattern.find(annotationContent)?.groupValues?.getOrNull(1)?.let {
                        tables.add(it)
                        virtualFiles.add(file)
                    }
                }
                else -> {
                    val valuePattern = Regex("value\\s*=\\s*[\"']([^\"']+)[\"']")
                    valuePattern.find(annotationContent)?.groupValues?.getOrNull(1)?.let {
                        extractFromSql(it, tables, file)
                    }
                }
            }
        }
    }

    private fun showResults(tables: List<String>, project: Project) {
        val dialog = JDialog()
        dialog.isModal = false
        dialog.isAlwaysOnTop = true
        val panel = JPanel(BorderLayout())
        panel.border = BorderFactory.createEmptyBorder(5, 5, 5, 5)

        val tabbedPane = JBTabbedPane()

        // First tab: Display all table names and support one click copying
        val resultTextArea = JTextArea(tables.joinToString(separator = "\n"))
        resultTextArea.isEditable = false
        val copyButton = JButton("Copy All")
        copyButton.addActionListener { 
            val selection = StringSelection(resultTextArea.text)
            Toolkit.getDefaultToolkit().systemClipboard.setContents(selection, null)
        }
        val firstPanel = JPanel(BorderLayout())
        firstPanel.add(JScrollPane(resultTextArea), BorderLayout.CENTER)
        firstPanel.add(copyButton, BorderLayout.SOUTH)
        tabbedPane.addTab("tables", firstPanel)

        // Second tab: Use a table to display a list of files associated with table names,
        // and support clicking to locate specific code locations
        val data = mutableListOf<Array<String>>()
        tables.forEach { table ->
            virtualFiles.forEach { file ->
                val content = readFileContent(file)
                val offset = findTableOffset(content, table)
                if (offset != -1) {
                    data.add(arrayOf(file.name, table))
                }
            }
        }

        val columnNames = arrayOf("file name", "table name")
        val tableModel = DefaultTableModel(data.toTypedArray(), columnNames)
        val table = JBTable(tableModel)
        // Monitor mouse click events
        table.addMouseListener(object : MouseAdapter() {
            override fun mouseClicked(e: MouseEvent) {
                if (e.clickCount == 1) {
                    val selectedRow = table.selectedRow
                    if (selectedRow != -1) {
                        val fileName = table.getValueAt(selectedRow, 0) as String
                        val tableName = table.getValueAt(selectedRow, 1) as String
                        val targetFile = virtualFiles.find { it.name == fileName }
                        targetFile?.let { file ->
                            val content = readFileContent(file)
                            val offset = findTableOffset(content, tableName)
                            if (offset != -1) {
                                OpenFileDescriptor(project, file, offset).navigate(true)
                            }
                        }
                    }
                }
            }
        })

        val secondPanel = JPanel(BorderLayout())
        secondPanel.add(JScrollPane(table), BorderLayout.CENTER)
        tabbedPane.addTab("tables and files", secondPanel)

        panel.add(tabbedPane, BorderLayout.CENTER)
        dialog.contentPane.add(panel)
        dialog.pack()
        dialog.setLocationRelativeTo(null)
        dialog.title = "Found ${tables.size} Tables"
        dialog.isVisible = true
        dialog.setSize(500, dialog.height)
    }

    private fun findTableOffset(content: String, tableName: String): Int {
        // Use boundary matching to avoid partial matching
        val pattern = Regex("\\b$tableName\\b")
        val matchResult = pattern.find(content)
        return matchResult?.range?.first ?: -1
    }
}